package com.pagepub.expo.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Set;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.core.simple.ParameterizedRowMapper;
import org.springframework.stereotype.Repository;

import com.pagepub.expo.entity.AppUser;
import com.pagepub.expo.entity.CorpNews;
@Repository
public class CorpNewsDao {
	@Autowired
	@Qualifier("jdbcTemplate")
	private JdbcTemplate template;
	
	@Autowired
	@Qualifier("appSeqDao")
	private AppSeqDao appSeqDao;
	

	
	private static String tableName="Corp_News";
	
	private static String QUERY_ALL = "SELECT id,title,content,clickNum,sequence," +
			"childFlag,addTime,updateTime,corpId,isAuditing, sortId "+
			 "FROM "+tableName;

	private static String QUERY_ALL_COUNT = "SELECT COUNT(*) FROM "+  tableName;
	
	private static String INSERT_SQL = "INSERT  INTO "+tableName+" (" +
			"id,title,content,clickNum,sequence," +
			"childFlag,addTime,updateTime,corpId,sortId,isAuditing)VALUES(" +
			":id,:title,:content,:clickNum,:sequence," +
			":childFlag,:addTime,:updateTime,:corpId,:sortId,:isAuditing)";

	private static String DELETE_SQL = "DELETE FROM "+ tableName +" WHERE id=?";

	private static String UPDATE_SQL = "UPDATE "+ tableName+" SET " +
			"title=:title,content=:content,clickNum=:clickNum,sequence=:sequence," +
			"childFlag=:childFlag,updateTime=:updateTime,corpId=:corpId,sortId=:sortId,isAuditing=:isAuditing "+
			"WHERE id=:id";

	private final ParameterizedRowMapper<CorpNews> mapper = new ParameterizedRowMapper<CorpNews>() {
		public CorpNews mapRow(ResultSet rs, int rowNum) throws SQLException {
			CorpNews bean = new CorpNews();
			bean.setId(rs.getLong("id"));
			bean.setTitle(rs.getString("title"));
			bean.setContent(rs.getString("content"));
			bean.setClickNum(rs.getInt("clickNum"));
			bean.setSequence(rs.getInt("sequence"));
			bean.setChildFlag(rs.getShort("childFlag"));
			bean.setAddTime(rs.getDate("addTime"));
			bean.setUpdateTime(rs.getDate("updateTime"));
			bean.setCorpId(rs.getString("corpId"));
			bean.setSortId(rs.getString("sortId"));
			bean.setIsAuditing(rs.getInt("isAuditing"));
			return bean;
		}
	};

	public List<CorpNews> findAll() {
		return template.query(QUERY_ALL, mapper);
	}

	public CorpNews findById(Long id) {
		List<CorpNews> appUsers = template.query(QUERY_ALL + " WHERE id=?",
				new Object[] { id }, mapper);
		if (appUsers.size() == 0) {
			return null;
		} else
			return appUsers.get(0);
	}
	
	
	
	public void insert(CorpNews bean) {
		if(bean.getId()==null){
			bean.setId(new Long(appSeqDao.getSequence("app_user_seq")));
		}		
		SqlParameterSource ps = new BeanPropertySqlParameterSource(bean);
		NamedParameterJdbcTemplate npTemplate = new NamedParameterJdbcTemplate(
				template.getDataSource());
		npTemplate.update(INSERT_SQL, ps);

	}

	public void delete(Long id) {
		template.update(DELETE_SQL, id);
	}

	public void update(CorpNews bean) {
		SqlParameterSource ps = new BeanPropertySqlParameterSource(bean);
		NamedParameterJdbcTemplate npTemplate = new NamedParameterJdbcTemplate(
				template.getDataSource());
		npTemplate.update(UPDATE_SQL, ps);
	}
	
	public int getCount(Map<String, String> param){
		int count=0;
		String sql = QUERY_ALL_COUNT+" ";
		int len = param.size();
		Iterator<String> keyIt = param.keySet().iterator();
		Object values[] = new Object[len];
		int i=0;
		while(keyIt.hasNext()){
			String key = keyIt.next();
			if(i==0){
				sql+=" where " + key +"=? ";
			}else{
				sql+="," + key +"=? ";
			}
			values[i++]=param.get(key);
		}
		count = template.queryForInt(sql);
		return count;
	}
	
	public List findByPage(int currentPage,int pageSize,Map<String, String> param){
		String sql = QUERY_ALL+" ";
		int len = param.size();
		Iterator<String> keyIt = param.keySet().iterator();
		Object values[] = new Object[len+2];
		int i=0;
		while(keyIt.hasNext()){
			String key = keyIt.next();
			if(i==0){
				sql+=" where " + key +"=? ";
			}else{
				sql+=" and " + key +"=? ";
			}
			values[i++]=param.get(key);
		}
		values[len]=currentPage;
		values[len+1]=pageSize;
		List lists = template.query(sql + " limit ?,?",values, mapper);
		return lists;
	}
	
	public void audit(Long id, boolean pass){
		int aduiting=0;
		if(pass==true){
			aduiting=1;
		}
		template.update("update " + tableName+" set isAuditing=? where id=?", new Object[]{aduiting,id});
	}
	
}
